USE SAS
GO

IF EXISTS (SELECT 1 from sysobjects where xtype = 'FN' AND NAME = 'GetParentAccountsXml')
	DROP FUNCTION dbo.GetParentAccountsXml
GO


CREATE FUNCTION [dbo].[GetParentAccountsXml] ()
RETURNS VARCHAR(MAX)
AS
BEGIN
	
	declare 
		@xml			varchar(max)

	SET @xml		= '<PARENTACCOUNTS><entry id="0"></entry>'

	SELECT @xml = @xml + '<entry id="' + CAST(PartnerNo AS VARCHAR) + '"><![CDATA['+ PartnerName +']]></entry>' 
	FROM TR_PARTNER WHERE PartnerCategory = 2 AND PartnerType = 1 AND isActive = 1
	ORDER BY [PartnerName]
	set @xml = @xml + '</PARENTACCOUNTS>'	

	return @xml
END


-- SELECT dbo.GetParentAccountsXml()